﻿Imports System.Data.SqlClient
Public Class SearchDepartmentOrganization3
    Inherits System.Web.UI.Page

    Dim con As New DBConnect
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'check if user signed in if not going back to login page 
        If Not Session.Item("UserIn").Equals("yes") Then
            Response.Redirect("default.aspx")
        End If
            If Not (IsPostBack) Then
                Dim SqlConnectString As String = con.SqlConnectString
                Dim myConnection As New SqlConnection(SqlConnectString)
                Dim myCommand As New SqlCommand()
                ' Dim result As Integer
                myCommand.Connection = myConnection
                'Set the SQLCommand object's properties for executing a Stored Procedure
                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.CommandText = "SelectAllDepartments"       'Name of the Stored Procedure to execute

                Try
                    myConnection.Open()
                    ddDepartments.DataSource = myCommand.ExecuteReader()
                    ddDepartments.DataValueField = "departmentID"
                    ddDepartments.DataTextField = "departmentName"

                    ddDepartments.DataBind()

                Catch ex As Exception
                    Throw ex
                Finally
                    myConnection.Close()
                    myConnection.Dispose()
                End Try
            End If

    End Sub

    Protected Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        If txtOrganization.Text.Equals("") Then
            lblSuccess.Visible = True
            lblSuccess.Text = "No match found"
        Else
            ''checking if input was more than 1 char and if no number was inputed
            'Dim inputStr As String = txtOrganization.Text
            'Dim resultCheck As Boolean
            'For i = 0 To inputStr.Length - 1
            '    If Not Char.IsLetter(inputStr.Chars(i)) Then
            '        resultCheck = False
            '    Else
            '        resultCheck = True
            '    End If
            'Next
            'If resultCheck = False Then
            '    ClientScript.RegisterStartupScript(Me.GetType, "alert", "alert('You have to use letters only');", True)
            'Else
            Dim SqlConnectString As String = con.SqlConnectString
            Dim myConnection As New SqlConnection(SqlConnectString)
            Dim myCommand As New SqlCommand()
            Dim orgName As String = txtOrganization.Text.Trim

            Dim result As Integer = -1
            myCommand.Connection = myConnection
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "SelectOrganization" '"SelectOrganization"       'Name of the Stored Procedure to execute

            Dim inputParameter As New SqlParameter("@organizationName", orgName)
            inputParameter.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter)

            'returning value of 0 if not found 
            Dim returnParameter As New SqlParameter("@new_identity", 0)
            returnParameter.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(returnParameter)

            Try
                myConnection.Open()


                
            Catch ex As Exception
                Throw ex
            Finally

                If Not IsDBNull(myCommand.Parameters("@new_identity").Value) Then
                    result = CType(myCommand.Parameters("@new_identity").Value, Integer)

                    If result = 1 Then
                        lblSuccess.Visible = True
                        lblSuccess.Text = "No match found"
                    Else
                      
                        Dim myCommand2 As New SqlCommand()
                        myCommand2.Connection = myConnection
                        'Set the SQLCommand object's properties for executing a Stored Procedure
                        myCommand2.CommandType = CommandType.StoredProcedure
                        myCommand2.CommandText = "SelectOrganizationSuccess" '"SelectOrganization"       'Name of the Stored Procedure to execute

                        Dim inputParameter99 As New SqlParameter("@organizationName", orgName)
                        inputParameter.Direction = ParameterDirection.Input
                        myCommand2.Parameters.Add(inputParameter99)

                        gvOrganizationsList.EmptyDataText = "No Records Found"
                        gvOrganizationsList.DataSource = myCommand2.ExecuteReader()
                        gvOrganizationsList.DataBind()
                        myConnection.Close()
                        myConnection.Open()
                        Dim i As Integer = 0
                        Dim r As Integer = 0
                        While i < gvOrganizationsList.Rows.Count
                            Dim departmentID As String
                            Dim depData As New DataTable
                            Dim SqlDr As SqlDataReader
                            Dim myCommand1 As New SqlCommand()

                            departmentID = CInt(gvOrganizationsList.Rows(r).Cells(1).Text)

                            myCommand1.Connection = myConnection
                            'Set the SQLCommand object's properties for executing a Stored Procedure
                            myCommand1.CommandType = CommandType.StoredProcedure
                            myCommand1.CommandText = "SelectDepartmentName"  'Name of the Stored Procedure to execute
                            Dim inputParameter20 As New SqlParameter("departmentID", departmentID)
                            inputParameter20.Direction = ParameterDirection.Input
                            myCommand1.Parameters.Add(inputParameter20)
                            SqlDr = myCommand1.ExecuteReader()
                            depData.Load(SqlDr)
                            gvOrganizationsList.Rows(r).Cells(1).Text = depData.Rows(0)(0)
                            i = i + 1
                            r = r + 1
                        End While
                    End If
                Else
                    gvOrganizationsList.DataBind()
                    lblSuccess.Visible = True
                    lblSuccess.Text = "No match found"
                End If

                myConnection.Close()
                myConnection.Dispose()
            End Try
            'End If
            End If
    End Sub

    Protected Sub ddDepartments_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddDepartments.SelectedIndexChanged
        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand()
        Dim depId As Integer = CInt(ddDepartments.SelectedItem.Value)

        Dim result As Integer = -1
        myCommand.Connection = myConnection
        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand.CommandType = CommandType.StoredProcedure
        'select org where dep id is ...
        myCommand.CommandText = "SelectOrganizationDep1"       'Name of the Stored Procedure to execute
        'MsgBox(depId) testing if right id number is used
        Dim inputParameter As New SqlParameter("@departmentID", depId)
        inputParameter.Direction = ParameterDirection.Input
        myCommand.Parameters.Add(inputParameter)

        'returning value of 0 if not found 
        Dim returnParameter As New SqlParameter("@new_identity", 0)
        returnParameter.Direction = ParameterDirection.Output
        myCommand.Parameters.Add(returnParameter)

        Try
            myConnection.Open()
            '  gvOrganizationsList.EmptyDataText = "No Records Found"
            '  gvOrganizationsList.DataSource = myCommand.ExecuteReader()


        Catch ex As Exception
            Throw ex
        Finally

            If Not IsDBNull(myCommand.Parameters("@new_identity").Value) Then
                result = CType(myCommand.Parameters("@new_identity").Value, Integer)

                If result = 1 Then
                    lblSuccess.Visible = True
                    lblSuccess.Text = "No match found"
                Else
                    lblSuccess.Visible = True
                    lblSuccess.Text = ""
                    Dim myCommand2 As New SqlCommand()
                    myCommand2.Connection = myConnection
                    'Set the SQLCommand object's properties for executing a Stored Procedure
                    myCommand2.CommandType = CommandType.StoredProcedure
                    myCommand2.CommandText = "SelectOrganizationDepSuccess" '"SelectOrganization"       'Name of the Stored Procedure to execute

                    Dim inputParameter99 As New SqlParameter("@departmentID", depId)
                    inputParameter.Direction = ParameterDirection.Input
                    myCommand2.Parameters.Add(inputParameter99)

                    gvOrganizationsList.EmptyDataText = "No Records Found"
                    gvOrganizationsList.DataSource = myCommand2.ExecuteReader()
                    gvOrganizationsList.DataBind()
                    myConnection.Close()
                    myConnection.Open()
                    Dim i As Integer = 0
                    Dim r As Integer = 0
                    While i < gvOrganizationsList.Rows.Count
                        Dim departmentID As String
                        Dim depData As New DataTable
                        Dim SqlDr As SqlDataReader
                        Dim myCommand1 As New SqlCommand()

                        departmentID = CInt(gvOrganizationsList.Rows(r).Cells(1).Text)

                        myCommand1.Connection = myConnection
                        'Set the SQLCommand object's properties for executing a Stored Procedure
                        myCommand1.CommandType = CommandType.StoredProcedure
                        myCommand1.CommandText = "SelectDepartmentName"  'Name of the Stored Procedure to execute
                        Dim inputParameter20 As New SqlParameter("departmentID", departmentID)
                        inputParameter20.Direction = ParameterDirection.Input
                        myCommand1.Parameters.Add(inputParameter20)
                        SqlDr = myCommand1.ExecuteReader()
                        depData.Load(SqlDr)
                        gvOrganizationsList.Rows(r).Cells(1).Text = depData.Rows(0)(0)
                        i = i + 1
                        r = r + 1
                    End While
                End If
            Else
                gvOrganizationsList.DataBind()
                lblSuccess.Visible = True
                lblSuccess.Text = "No match found"
            End If


            myConnection.Close()
            myConnection.Dispose()
        End Try

    End Sub



    'Deleting Admin From Database fired up when user clicks delete
    Protected Sub gvAdminList_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles gvOrganizationsList.RowDeleting

        Dim rowIndex As Integer = e.RowIndex
        Dim id As Integer = CInt(gvOrganizationsList.DataKeys(rowIndex).Value)
        Dim orgName As String = CStr(gvOrganizationsList.Rows(rowIndex).Cells(0).Text)
        Dim result As Integer
        'connecting to database 
        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand()
        myCommand.Connection = myConnection

        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "RemoveOrganization"       'Name of the Stored Procedure to execute

        Dim inputParameter As New SqlParameter("@organizationID", id)
        inputParameter.Direction = ParameterDirection.Input
        myCommand.Parameters.Add(inputParameter)

        Dim returnParameter As New SqlParameter("@new_identity", 0)
        returnParameter.Direction = ParameterDirection.Output
        myCommand.Parameters.Add(returnParameter)
        Try
            myConnection.Open()
            myCommand.ExecuteScalar()

        Catch ex As Exception
            Throw ex
        Finally
            If Not IsDBNull(myCommand.Parameters("@new_identity").Value) Then
                result = CType(myCommand.Parameters("@new_identity").Value, Integer)

                If result = 0 Then
                    lblSuccess.Visible = True
                    lblSuccess.Text = "Success! Organization: " + orgName + " has been Removed."

                Else
                    lblSuccess.Visible = True
                    lblSuccess.Text = "Sorry but there was an error try refreshing page and try again"
                End If
            Else

                lblSuccess.Visible = True
                lblSuccess.Text = "Success! Organization: " + orgName + " has been Removed."
            End If
            myConnection.Close()
            myConnection.Dispose()
        End Try
    End Sub

    Protected Sub btnAll_Click(sender As Object, e As EventArgs) Handles btnAll.Click
        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand()
        myCommand.Connection = myConnection
        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand.CommandType = CommandType.StoredProcedure
        'select org where dep id is ...
        myCommand.CommandText = "SelectAllOrganizations"       'Name of the Stored Procedure to execute
        Try
            myConnection.Open()
            gvOrganizationsList.EmptyDataText = "No Records Found"
            gvOrganizationsList.DataSource = myCommand.ExecuteReader()
        Catch ex As Exception
            Throw ex
        Finally
            gvOrganizationsList.DataBind()
            myConnection.Close()
            myConnection.Open()
            Dim i As Integer = 0
            Dim r As Integer = 0

            While i < gvOrganizationsList.Rows.Count
                Dim departmentID As String
                Dim depData As New DataTable
                Dim SqlDr As SqlDataReader
                Dim myCommand1 As New SqlCommand()

                departmentID = CInt(gvOrganizationsList.Rows(r).Cells(1).Text)

                myCommand1.Connection = myConnection
                'Set the SQLCommand object's properties for executing a Stored Procedure
                myCommand1.CommandType = CommandType.StoredProcedure
                myCommand1.CommandText = "SelectDepartmentName"  'Name of the Stored Procedure to execute
                Dim inputParameter20 As New SqlParameter("departmentID", departmentID)
                inputParameter20.Direction = ParameterDirection.Input
                myCommand1.Parameters.Add(inputParameter20)
                SqlDr = myCommand1.ExecuteReader()
                depData.Load(SqlDr)
                gvOrganizationsList.Rows(r).Cells(1).Text = depData.Rows(0)(0)
                i = i + 1
                r = r + 1
            End While
            myConnection.Close()
            myConnection.Dispose()
        End Try
    End Sub

   
End Class